IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ActualizarPrestamo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ActualizarPrestamo]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CrearLibro]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_CrearLibro]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CrearPrestamo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_CrearPrestamo]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CrearReserva]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_CrearReserva]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CrearSocio]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_CrearSocio]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CrearUsuario]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_CrearUsuario]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_EliminarLibro]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_EliminarLibro]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_EliminarSocio]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_EliminarSocio]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_EliminarPrestamo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_EliminarPrestamo]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_EliminarReserva]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_EliminarReserva]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ModificarLibro]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ModificarLibro]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ModificarPrestamo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ModificarPrestamo]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ModificarReserva]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ModificarReserva]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ModificarSocio]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ModificarSocio]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ValidarUsuario]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ValidarUsuario]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_VerCatalogo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_VerCatalogo]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_VerPrestamosSocio]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_VerPrestamosSocio]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_VerReservasSocio]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_VerReservasSocio]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_VerificarLibrosPendientes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_VerificarLibrosPendientes]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ConsultarDatosSocio]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ConsultarDatosSocio]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ObtenerPrestamosPorSocio]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ObtenerPrestamosPorSocio]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ObtenerReservasPorSocio]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ObtenerReservasPorSocio]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_VerReservasPorLibro]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_VerReservasPorLibro]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_VerPrestamosVencidosSocio]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_VerPrestamosVencidosSocio]


USE [Biblioteca]
GO
/****** Object:  StoredProcedure [dbo].[sp_ActualizarPrestamo]    Script Date: 09/06/2012 22:26:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:              <Author,,Name>
-- Create date: <02/09/2012>
-- Description: <SP que permite actualizar un prestamo cuando un libro es devuelto>
-- =============================================


CREATE PROCEDURE [dbo].[sp_ActualizarPrestamo]
@idPrestamo int

AS
BEGIN

UPDATE Prestamo
SET fechaRealDevolucion = getdate()
WHERE idPrestamo = @idPrestamo

END

GO
---------------------------------------------------------------


USE [Biblioteca]

GO

/****** Object:  StoredProcedure [dbo].[sp_CrearLibro]    Script Date: 09/16/2012 16:11:36 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================

-- Author:              <Author,,Name>

-- Create date: <02/09/2012>

-- Description: <SP que permite registrar un libro>

-- =============================================



CREATE PROCEDURE [dbo].[sp_CrearLibro]

@titulo nvarchar(50),
@autor nvarchar(50),
@isbn nvarchar(20),
@cantEj int,
@alta_libro bit OUTPUT

AS
BEGIN

INSERT INTO Libro (titulo,autor,isbn,cantEj)
VALUES (@titulo,@autor,@isbn,@cantEj)

if(SELECT idLibro FROM Libro WHERE titulo=@titulo) is null
set @alta_libro = 0
else
set @alta_libro = 1
END
GO
---------------------------------------------------------------

USE [Biblioteca]
GO
/****** Object:  StoredProcedure [dbo].[sp_CrearPrestamo]    Script Date: 09/16/2012 16:12:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================

-- Author:              <Author,,Name>

-- Create date: <02/09/2012>

-- Description: <SP que permite registrar un nuevo prestamo>

-- =============================================




CREATE PROCEDURE [dbo].[sp_CrearPrestamo]

@fechaPedido datetime,
@idLibro int,
@idSocio int,
@fechaDevolucion datetime,

@alta_prestamo bit OUTPUT

AS

BEGIN

INSERT INTO Prestamo (fechaPedido,idLibro,idSocio,fechaDevolucion,fechaRealDevolucion)
VALUES (@fechaPedido,@idLibro,@idSocio,@fechaDevolucion,null)

if(SELECT idPrestamo FROM Prestamo WHERE fechaPedido=@fechaPedido and idLibro = @idLibro) is null
set @alta_prestamo = 0
else
set @alta_prestamo = 1
END
GO

--------------------------------------------------------

USE [Biblioteca]
GO
/****** Object:  StoredProcedure [dbo].[sp_CrearReserva]    Script Date: 09/16/2012 16:12:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================

-- Author:              <Author,,Name>

-- Create date: <02/09/2012>

-- Description: <SP que permite registrar crear una reserva>

-- =============================================


CREATE PROCEDURE [dbo].[sp_CrearReserva]

@fechaReserva datetime,
@idSocio int,
@idLibro int,
@fechaFinReserva datetime,
@alta_reserva bit OUTPUT

AS
BEGIN

INSERT INTO Reserva (fechaReserva,idSocio,idLibro,fechaFinReserva)
VALUES (@fechaReserva,@idSocio,@idLibro,@fechaFinReserva)

if(SELECT idReserva FROM Reserva WHERE fechaReserva=@fechaReserva and idSocio = @idSocio ) is null
set @alta_reserva = 0
else
set @alta_reserva = 1
END
GO

------------------------------------------------------------



USE [Biblioteca]
GO
/****** Object:  StoredProcedure [dbo].[sp_CrearSocio]    Script Date: 09/16/2012 16:12:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================

-- Author:              <Author,,Name>

-- Create date: <02/09/2012>

-- Description: <SP que permite registrar un nuevo usuario>

-- =============================================

CREATE PROCEDURE [dbo].[sp_CrearSocio]

@dni int,
@nombre nvarchar(50),
@nomUsuario nvarchar(60),
@password nvarchar(40),
@email nvarchar(50),
@alta_usuario bit OUTPUT

AS
BEGIN
INSERT INTO Socio (nombre,dni,password,nomUsuario,mail)
VALUES (@nombre,@dni,@password,@nomUsuario,@email)

if(SELECT idSocio FROM Socio WHERE dni=@dni) is null

set @alta_usuario = 0
else
set @alta_usuario = 1
END
GO
----------------------------------------------

USE [Biblioteca]
GO
/****** Object:  StoredProcedure [dbo].[sp_CrearUsuario]    Script Date: 09/02/2012 10:50:36 ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:              <Author,,Name>
-- Create date: <02/09/2012>
-- Description: <SP que permite registrar un nuevo usuario>
-- =============================================

CREATE PROCEDURE [dbo].[sp_CrearUsuario]
@idSocio int, 
@dni int,
@nombre nvarchar(50),
@nomUsuario nvarchar(60),
@password nvarchar(40),
@email nvarchar(50),
@alta_usuario bit OUTPUT

AS
BEGIN

INSERT INTO Socio (nombre,dni,password,nomUsuario,mail)
VALUES (@nombre,@dni,@password,@nomUsuario,@email)

if(SELECT idSocio FROM Socio WHERE dni=@dni) is not null
set @alta_usuario = 1
else
set @alta_usuario = 0
END
GO


------------------------------------------------------


USE [Biblioteca]
GO
/****** Object:  StoredProcedure [dbo].[sp_EliminarLibro]    Script Date: 09/16/2012 16:13:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================

-- Author:              <Author,,Name>

-- Create date: <09/08/2012>

-- Description: <SP que permite eliminar un libro>

-- Nota: idLibro es FK de la tabla Reserva y Prestamo

-- =============================================





CREATE PROCEDURE [dbo].[sp_EliminarLibro]

@idLibro int,
@baja_libro bit OUTPUT

AS
BEGIN
DELETE
FROM Libro
WHERE idLibro=@idLibro


if(SELECT idLibro FROM Libro WHERE idLibro=@idLibro) is null
set @baja_libro = 1
else
set @baja_libro = 0
END
GO

--------------------------------------------


USE [Biblioteca]
GO
/****** Object:  StoredProcedure [dbo].[sp_EliminarPrestamo]    Script Date: 09/16/2012 16:13:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================

-- Author:              <Author,Name>

-- Create date: <02/09/2012>

-- Description: <SP que permite eliminar un prestamo>

-- =============================================




CREATE PROCEDURE [dbo].[sp_EliminarPrestamo]

@idPrestamo int,
@baja_prestamo bit OUTPUT

AS
BEGIN
DELETE
FROM Prestamo
WHERE idPrestamo=@idPrestamo

if(SELECT idPrestamo FROM Prestamo WHERE idPrestamo=@idPrestamo) is null
set @baja_prestamo = 1
else
set @baja_prestamo = 0
END 
GO

-----------------------------------------------------

USE [Biblioteca]
GO
/****** Object:  StoredProcedure [dbo].[sp_EliminarReserva]    Script Date: 09/16/2012 16:14:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================

-- Author:              <Author,Name>

-- Create date: <02/09/2012>

-- Description: <SP que permite eliminar una reserva>

-- =============================================




CREATE PROCEDURE [dbo].[sp_EliminarReserva]

@idReserva int,
@baja_reserva bit OUTPUT

AS
BEGIN
DELETE
FROM Reserva
WHERE idReserva=@idReserva

if(SELECT idReserva FROM Reserva WHERE idReserva=@idReserva) is null
set @baja_reserva = 1
else
set @baja_reserva = 0
END

GO

--------------------------------------------------


-- =============================================

-- Author:              <Author,Name>

-- Create date: <02/09/2012>

-- Description: <SP que permite eliminar un usuario>

-- =============================================

CREATE PROCEDURE [dbo].[sp_EliminarSocio]

@idSocio int,
@baja_socio bit OUTPUT

AS

BEGIN
DELETE
FROM Socio
WHERE idSocio=@idSocio

if(SELECT idSocio FROM Socio WHERE idSocio=@idSocio) is null
set @baja_socio = 1
else
set @baja_socio = 0

END
GO


-------------------------------------

USE [Biblioteca]
GO
/****** Object:  StoredProcedure [dbo].[sp_ModificarLibro]    Script Date: 09/16/2012 16:15:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Author:              <Author,Name>

-- Create date: <02/09/2012>

-- Description: <SP que permite modificar un libro>

-- =============================================

CREATE PROCEDURE [dbo].[sp_ModificarLibro]

@idLibro int,
@titulo nvarchar(50),
@autor nvarchar(50),
@isbn nvarchar(20),
@cantEj int,
@update_libro bit OUTPUT

AS
BEGIN
UPDATE Libro
SET titulo=@titulo,autor=@autor,isbn=@isbn,cantEj=@cantEj
WHERE idLibro=@IdLibro
END
GO
-------------------------------------------------------


USE [Biblioteca]
GO
/****** Object:  StoredProcedure [dbo].[sp_ModificarPrestamo]    Script Date: 09/16/2012 16:15:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================

-- Author:              <Author,Name>

-- Create date: <02/09/2012>

-- Description: <SP que permite modificar prestamo>

-- =============================================
CREATE PROCEDURE [dbo].[sp_ModificarPrestamo]

@idPrestamo int, 
@fechaPedido datetime,
@idLibro int,
@idSocio int,
@fechaDevolucion datetime,
@fechaRealDevolucion datetime,
@update_prestamo bit OUTPUT


AS
BEGIN
UPDATE Prestamo
SET fechaPedido= @fechaPedido,idLibro=@idLibro,idSocio=@idSocio,fechaDevolucion=@fechaDevolucion,fechaRealDevolucion=fechaRealDevolucion
WHERE idPrestamo=@idPrestamo
END
GO

---------------------------------------------------------------------


USE [Biblioteca]
GO
/****** Object:  StoredProcedure [dbo].[sp_ModificarReserva]    Script Date: 09/16/2012 16:16:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================

-- Author:              <Author,Name>

-- Create date: <02/09/2012>

-- Description: <SP que permite modificar una reserva>

-- =============================================




CREATE PROCEDURE [dbo].[sp_ModificarReserva]

@idReserva int, 
@fechaReserva datetime,
@idSocio int,
@idLibro int,
@fechaFinReserva datetime,
@update_reserva bit OUTPUT


AS
BEGIN
UPDATE Reserva
SET fechaReserva=@fechaReserva,idSocio=@idSocio,idLibro=@idLibro,fechaFinReserva=@fechaFinReserva
WHERE idReserva=@idReserva
END
GO

-------------------------------------------------------------


USE [Biblioteca]
GO

/****** Object:  StoredProcedure [dbo].[sp_ModificarSocio]    Script Date: 09/16/2012 16:16:27 ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:              <Author,,Name>

-- Create date: <02/09/2012>

-- Description: <SP que permite registrar un nuevo usuario>

-- =============================================
CREATE PROCEDURE [dbo].[sp_ModificarSocio]

@idSocio int, 
@dni int,
@nombre nvarchar(50),
@nomUsuario nvarchar(60),
@password nvarchar(40),
@email nvarchar(50),
@update_socio bit OUTPUT

AS
BEGIN
UPDATE Socio
SET nombre=@nombre,dni=@dni,password=@password,nomUsuario=@nomUsuario,mail=@email
WHERE idSocio=@idSocio
END
GO
------------------------------------------------------------


USE [Biblioteca]
GO
/****** Object:  StoredProcedure [dbo].[sp_ValidarUsuario]    Script Date: 09/01/2012 10:50:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:              <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
Create PROCEDURE [dbo].[sp_ValidarUsuario]

@Usuario nvarchar(50) , 
@Password nvarchar(50),
@tiene_acceso bit OUTPUT

AS
BEGIN
if (SELECT Top 1 nombre From Socio WHERE nomUsuario = @Usuario
and password = @Password) is null
set @tiene_acceso = 0
else
set @tiene_acceso = 1
END
GO

---------------------------------------------------------

USE [Biblioteca]
GO
/****** Object:  StoredProcedure [dbo].[sp_ConsultarDatosSocio]    Script Date: 09/01/2012 10:50:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:              <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
Create PROCEDURE [dbo].[sp_ConsultarDatosSocio]

@nomUsuario nvarchar(50) 


AS
BEGIN

SELECT idSocio AS codigo , nombre as nombre,password as pass,mail as mail, dni as dni From Socio WHERE nomUsuario = @nomUsuario

END
GO
 
---------------------------------------------------------


USE [Biblioteca]

GO

/****** Object:  StoredProcedure [dbo].[sp_VerCatalogo]    Script Date: 09/16/2012 16:16:54 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================

-- Author:              <Author,,Name>

-- Create date: <02/09/2012>

-- Description: <SP que permite ver el catalogo de libros>

-- =============================================

Create PROCEDURE [dbo].[sp_VerCatalogo]

AS
BEGIN
SELECT l.idLibro AS idLibro,l.autor AS autor, l.titulo AS titulo, l.cantEj AS cantEj, l.isbn AS isbn
FROM Libro l
END
GO

------------------------------------------


USE [Biblioteca]
GO
/****** Object:  StoredProcedure [dbo].[sp_VerPrestamosSocio]    Script Date: 09/16/2012 16:17:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================

-- Author:              <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================


CREATE PROCEDURE [dbo].[sp_VerPrestamosSocio]

@idSocio int

AS
BEGIN
SELECT *
FROM Prestamo
WHERE idSocio=@idSocio
END
GO

------------------------------------


USE [Biblioteca]
GO
/****** Object:  StoredProcedure [dbo].[sp_VerReservasSocio]    Script Date: 09/16/2012 16:17:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Author:              <Author,,Name>

-- Create date: <02/09/2012>

-- Description: <SP que permite listar las reservas de un socio en base a su ID>

-- =============================================

CREATE PROCEDURE [dbo].[sp_VerReservasSocio]

@idSocio int

AS
BEGIN
SELECT *
FROM Reserva 
WHERE idSocio=@idSocio 
END
GO
------------------------------------------------


USE [Biblioteca]
GO
/****** Object:  StoredProcedure [dbo].[sp_ValidarUsuario]    Script Date: 09/06/2012 22:07:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: VerificarLibrosPendientes.sql|9|0|C:\Users\Cynthia\Desktop\Tp Electiva\db\scripts\store procedures\ValidarUsuario.sql

-- =============================================
-- Author:              <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_VerificarLibrosPendientes]

@idSocio int , 
@tiene_libros_pendientes bit OUTPUT

AS
BEGIN
if NOT EXISTS(SELECT * From Prestamo WHERE idSocio = @idSocio and  fechaRealDevolucion=null)
set @tiene_libros_pendientes = 0
else
set @tiene_libros_pendientes = 1
END
GO

------------------------------------------------------------

USE [Biblioteca]
GO
/****** Object:  StoredProcedure [dbo].[sp_ValidarUsuario]    Script Date: 09/06/2012 22:07:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: VerificarLibrosPendientes.sql|9|0|C:\Users\Cynthia\Desktop\Tp Electiva\db\scripts\store procedures\ValidarUsuario.sql

-- =============================================
-- Author:              <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_ObtenerReservasPorSocio]
@idSocio int

AS
BEGIN
SELECT r.fechaReserva AS fechaReserva, l.titulo AS titulo, l.autor AS autor, l.isbn AS isbn, l.cantEj AS cantEj
FROM Reserva r INNER JOIN Libro l ON l.idLibro = r.idLibro
WHERE idSocio=@idSocio 
END
GO

-----------------------------------------------------------------
USE [Biblioteca]
GO
/****** Object:  StoredProcedure [dbo].[sp_ValidarUsuario]    Script Date: 09/06/2012 22:07:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: VerificarLibrosPendientes.sql|9|0|C:\Users\Cynthia\Desktop\Tp Electiva\db\scripts\store procedures\ValidarUsuario.sql

-- =============================================
-- Author:              <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_ObtenerPrestamosPorSocio]
@idSocio int

AS
BEGIN
SELECT p.fechaDevolucion AS fechaDevolucion,p.idLibro AS idLibro, l.titulo AS titulo, l.autor AS autor, l.isbn AS isbn, l.cantEj AS cantEj
FROM Prestamo p INNER JOIN Libro l ON l.idLibro = p.idLibro
WHERE idSocio=@idSocio 
END
GO


USE [Biblioteca]
GO
/****** Object:  StoredProcedure [dbo].[sp_VerReservasPorLibro]    Script Date: 09/06/2012 22:07:04 ******/
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Author:              <Author,,Name>

-- Create date: <02/09/2012>

-- Description: <SP que permite listar las reservas de un socio en base a su ID>

-- =============================================

CREATE PROCEDURE [dbo].[sp_VerReservasPorLibro]

@idLibro int

AS
BEGIN
SELECT *
FROM Reserva 
WHERE idLibro=@idLibro 
END
GO

USE [Biblioteca]
GO
/****** Object:  StoredProcedure [dbo].[sp_VerPrestamosVencidosSocio]    Script Date: 09/06/2012 22:07:04 ******/



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

-- =============================================

-- Author:              <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================


CREATE PROCEDURE [dbo].[sp_VerPrestamosVencidosSocio]

@idSocio int

AS
BEGIN
SELECT *
FROM Prestamo
WHERE idSocio=@idSocio
END

SELECT *
FROM Prestamo where idSocio=@idSocio and getdate() >  fechaDevolucion 
